package com.wenjie.databseDiff.handle.DatasourceSynchronizer.imp;

import cn.hutool.core.util.BooleanUtil;
import com.wenjie.databseDiff.enums.Drivers;
import com.wenjie.databseDiff.handle.DatasourceSynchronizer.AbstractDatasourceSynchronizer;
import com.wenjie.original.entity.SysDiffColumn;
import com.wenjie.original.entity.SysDiffTable;
import lombok.extern.slf4j.Slf4j;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Mysql数据源同步
 *
 * @author wxg
 */
@Slf4j
public class MysqlDatasourceSynchronizer extends AbstractDatasourceSynchronizer {

    private static final String SQL_QUERY_TABLE = "select * from information_schema.tables where table_schema = ?";
    private static final String SQL_QUERY_COLUMN = "select *  from information_schema.columns where table_schema = ? and table_name=?";
    private static final String SQL_QUERY_INDEX = "select * " + "from (SELECT t.TABLE_SCHEMA, " + "             t.TABLE_NAME, " + "             t.INDEX_NAME, " + "             GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS `COLUMNS` " + "      FROM information_schema.statistics t " + "      GROUP BY t.TABLE_SCHEMA, t.TABLE_NAME, t.INDEX_NAME) as tb where tb.TABLE_SCHEMA =?";


    private static final String SQL_QUERY_INDEX_DETAIL = "SHOW INDEX FROM ";


    public MysqlDatasourceSynchronizer(String url, String user, String password) {
        super(url, user, password);
    }


    @Override
    public List<SysDiffTable> getTables(Connection connection, String database) {
        List<SysDiffTable> allTables = getAllTables(connection, database);
        log.info("Find {} tables", allTables.size());
        Map<String, List<String>> allTableIndexes = getAllTableIndexes(connection, database);
        log.info("Find {} indexes", allTableIndexes.size());
        for (SysDiffTable SysDiffTable : allTables) {
            List<String> indexes = allTableIndexes.get(SysDiffTable.getTableName());
            if (indexes != null) {
                SysDiffTable.setTableIndex(String.join("; ", indexes));

                // 获取更详细的index信息 -- 扩展生成创建index的sql
                Map<String, String> indexDetails = getTableIndexDetails(connection, SysDiffTable.getTableName());
                SysDiffTable.setTableIndexDetail(indexDetails);
            }
        }
        return allTables;
    }

    private Map<String, String> getTableIndexDetails(Connection connection, String tableName) {
        HashMap<String, String> returnMap = new HashMap<>();
        try {
            PreparedStatement statement = connection.prepareStatement(SQL_QUERY_INDEX_DETAIL + tableName);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                boolean nonUnique = resultSet.getBoolean("Non_unique"); // 是否不唯一
                String keyName = resultSet.getString("Key_name");// 索引的名称
                String columnName = resultSet.getString("Column_name");// 字段的名称
                String collation = resultSet.getString("Collation");// 排序规则
                String subPart = resultSet.getString("Sub_part");// 是否为单列索引 只能处理单列索引
                String indexType = resultSet.getString("Index_type");// 索引类型
                String indexComment = resultSet.getString("Index_comment");// 索引的注释

                // 拼接索引
                StringBuffer indexSql = new StringBuffer();
                if (subPart == null) {
                    indexSql.append("ALTER TABLE ");
                    indexSql.append(tableName);
                    indexSql.append(" ADD ");

                    if ("PRIMARY".equals(keyName)) {
                        indexSql.append("PRIMARY KEY (`");
                        indexSql.append(columnName);
                        indexSql.append("`)");
                    } else {
                        if (!nonUnique) indexSql.append("UNIQUE ");
                        indexSql.append("INDEX ");
                        indexSql.append("`");
                        indexSql.append(keyName);
                        indexSql.append("` ");
                        indexSql.append("(`");
                        indexSql.append(columnName);
                        indexSql.append("`");
                        if ("D".equals(collation)) indexSql.append(" DESC");
                        indexSql.append(") ");
                        indexSql.append("USING ");
                        indexSql.append(indexType);
                        if (indexComment != null) {
                            indexSql.append(" COMMENT '");
                            indexSql.append(indexComment);
                            indexSql.append("'");
                        }

                    }
                    indexSql.append(";");
                }
                returnMap.put(keyName, indexSql.toString());
            }
            return returnMap;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    private List<SysDiffTable> getAllTables(Connection connection, String database) {
        PreparedStatement statement;
        try {
            statement = connection.prepareStatement(SQL_QUERY_TABLE);

            statement.setString(1, database);
            ResultSet resultSet = statement.executeQuery();
            List<SysDiffTable> tableInfos = new ArrayList<>();
            while (resultSet.next()) {
                SysDiffTable SysDiffTable = new SysDiffTable();
                SysDiffTable.setTableName(resultSet.getString("TABLE_NAME"));
                SysDiffTable.setTableType(resultSet.getString("TABLE_TYPE"));
                SysDiffTable.setTableRows(resultSet.getLong("TABLE_ROWS"));
                SysDiffTable.setTableComment(resultSet.getString("TABLE_COMMENT"));
                SysDiffTable.setTableCollation(resultSet.getString("TABLE_COLLATION"));
                SysDiffTable.setTableEngine(resultSet.getString("ENGINE"));
                tableInfos.add(SysDiffTable);
            }
            return tableInfos;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    private Map<String, List<String>> getAllTableIndexes(Connection connection, String database) {
        Map<String, List<String>> indexMap = new HashMap<>();
        PreparedStatement statement;
        try {
            statement = connection.prepareStatement(SQL_QUERY_INDEX);
            statement.setString(1, database);
            ResultSet indexResultSet = statement.executeQuery();
            while (indexResultSet.next()) {
                String tableName = indexResultSet.getString("TABLE_NAME");
                String indexName = indexResultSet.getString("INDEX_NAME");
                String indexColumn = indexResultSet.getString("COLUMNS");
                List<String> list = indexMap.computeIfAbsent(tableName, k -> new ArrayList<>());
                list.add(indexName + "(" + indexColumn + ")");
            }
            return indexMap;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


    @Override
    public List<SysDiffColumn> getColumns(Connection connection, String database, String tableName) {

        List<SysDiffColumn> columnInfos = new ArrayList<>();
        PreparedStatement statement;
        try {
            statement = connection.prepareStatement(SQL_QUERY_COLUMN);

            statement.setString(1, database);
            statement.setString(2, tableName);
            ResultSet resultSet = statement.executeQuery();

            while (resultSet.next()) {
                SysDiffColumn SysDiffColumn = new SysDiffColumn();
                SysDiffColumn.setColumnName(resultSet.getString("COLUMN_NAME"));
                SysDiffColumn.setColumnSort(resultSet.getInt("ORDINAL_POSITION"));
                SysDiffColumn.setColumnDefault(resultSet.getObject("COLUMN_DEFAULT"));
                SysDiffColumn.setColumnDataType(resultSet.getString("DATA_TYPE"));
                SysDiffColumn.setColumnType(resultSet.getString("COLUMN_TYPE"));
                SysDiffColumn.setColumnIndex(resultSet.getString("COLUMN_KEY"));
                SysDiffColumn.setColumnNull(BooleanUtil.toBoolean(resultSet.getString("IS_NULLABLE")));
                SysDiffColumn.setColumnComment(resultSet.getString("COLUMN_COMMENT"));
                SysDiffColumn.setTableName(resultSet.getString("TABLE_NAME"));
                columnInfos.add(SysDiffColumn);
            }
            return columnInfos;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public String getDriverFile() {
        return Drivers.MYSQL.getDriver();
    }

    @Override
    public String getDriverClassName() {
        return Drivers.MYSQL.getDriverClassName();
    }

}
